﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;
using System.Windows.Forms.DataVisualization.Charting;
using System.IO;
using System.Text;

namespace Avivo
{
	public partial class ChartView : UserControl
	{
		#region Private members
		private List<string> xval;
		private List<List<object>> yvals;
		private List<string> headers;
		#endregion

		#region Constructor
		public ChartView()
		{
			InitializeComponent();
			this.ChartType = SeriesChartType.Column;
		}
		#endregion

		#region Public properties
		public SeriesChartType ChartType
		{
			get;
			set;
		}

		public string ConnectionString
		{
			get;
			set;
		}

		public string Query
		{
			get;
			set;
		}
		#endregion

		#region Public methods
		public void Generate()
		{
			using (SqlConnection con = new SqlConnection(this.ConnectionString))
			{
				con.Open();
				using (SqlCommand command = new SqlCommand(this.Query, con))
				{
					#region Chart style
					chart.BackColor = Color.WhiteSmoke;
					chart.BackSecondaryColor = Color.White; //Gainsboro, AliceBlue
					chart.BackGradientStyle = GradientStyle.TopBottom;

					chart.ChartAreas.Clear();
					chart.ChartAreas.Add("Default");
					chart.ChartAreas["Default"].AxisX.IsMarginVisible = true;

					var color = Color.FromArgb(70, 64, 64, 64); //Gray
					var color2 = Color.FromArgb(20, 64, 64, 64); //Light gray

					chart.ChartAreas["Default"].AxisX.LineColor = color;
					chart.ChartAreas["Default"].AxisX.MajorGrid.LineColor = color;
					chart.ChartAreas["Default"].AxisX.MinorGrid.Enabled = true;
					chart.ChartAreas["Default"].AxisX.MinorGrid.LineColor = color2;

					chart.ChartAreas["Default"].AxisY.LineColor = color;
					chart.ChartAreas["Default"].AxisY.MajorGrid.LineColor = color;
					chart.ChartAreas["Default"].AxisY.MinorGrid.Enabled = true;
					chart.ChartAreas["Default"].AxisY.MinorGrid.LineColor = color2;

					chart.ChartAreas["Default"].AxisY2.Enabled = AxisEnabled.True;
					chart.ChartAreas["Default"].AxisY2.LineColor = color;
					chart.ChartAreas["Default"].AxisY2.MajorTickMark.Enabled = false;
					chart.ChartAreas["Default"].AxisY2.MajorGrid.Enabled = false;
					chart.ChartAreas["Default"].AxisY2.MinorGrid.Enabled = false;
					chart.ChartAreas["Default"].AxisY2.LabelStyle.Enabled = false;

					chart.ChartAreas["Default"].BorderColor = color;

					chart.Series.Clear();
					#endregion

					xval = new List<string>();
					yvals = new List<List<object>>();
					headers = new List<string>();

					//Get points from database
					var reader = command.ExecuteReader();

					for (int i = 0; i < reader.FieldCount; i++)
					{
						string name = reader.GetName(i);
						headers.Add(name);
					}

					while (reader.Read())
					{
						int count = reader.FieldCount;
						for (int i = 0; yvals.Count < count - 1 && i < 10; i++)
						{
							yvals.Add(new List<object>());
						}

						for (int i = 0; i < count && i <= 10; i++)
						{
							if (i == 0)
							{
								xval.Add("" + reader[i]);
							}
							else
							{
								yvals[i - 1].Add(reader[i]);
							}
						}
					}

					//Populate the chart with points
					for (int i = 0; i < yvals.Count; i++)
					{
						string name = headers[i + 1] ?? "Series" + i;
						chart.Series.Add(name);

						if (yvals[i].Count > 200)
						{
							//Use "fast" chart type for large amount of points
							switch (this.ChartType)
							{
								case SeriesChartType.Point:
									chart.Series[name].ChartType = SeriesChartType.FastPoint;
									break;

								default:
								case SeriesChartType.Line:
									chart.Series[name].ChartType = SeriesChartType.FastLine;
									break;
							}
						}
						else
						{
							//User-defined chart type
							chart.Series[name].ChartType = this.ChartType;
						}

						chart.Series[name].IsValueShownAsLabel = true;
						chart.Series[name].Points.DataBindXY(xval, yvals[i]);
					}
				}
			}
		}

		public void TryGenerate()
		{
			try
			{
				Generate();
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message);
			}
		}

		public void SaveImageDialog()
		{
			if (xval == null || yvals == null || yvals.Count == 0)
			{
				//No data to save
				MessageBox.Show("No data to export. Render the chart first.", "SQL Chart", MessageBoxButtons.OK, MessageBoxIcon.Warning);
				return;
			}

			var dialog = new SaveFileDialog();
			dialog.Filter = "Image (*.png)|*.png";
			if (dialog.ShowDialog() == DialogResult.OK)
			{
				SaveImage(dialog.FileName);
			}
		}

		public void SaveDataDialog()
		{
			if (xval == null || yvals == null || yvals.Count == 0)
			{
				//No data to save
				MessageBox.Show("No data to export. Render the chart first.", "SQL Chart", MessageBoxButtons.OK, MessageBoxIcon.Warning);
				return;
			}

			//Build CSV document
			const string separator = ";";
			var builder = new StringBuilder();

			foreach (string header in headers)
			{
				builder.Append(builder.Length > 0 ? separator : "").Append(header);
			}

			for (int i = 0; i < xval.Count; i++)
			{
				builder.AppendLine();
				builder.Append(xval[i]);
				foreach (var yval in yvals)
				{
					builder.Append(separator);
					builder.Append(yval[i]);
				}
			}

			string csv = builder.ToString();

			//Save to file
			var dialog = new SaveFileDialog();
			dialog.Filter = "Data (*.csv)|*.csv";
			if (dialog.ShowDialog() == DialogResult.OK)
			{
				File.WriteAllText(dialog.FileName, csv);
			}
		}

		public void SaveReportDialog()
		{
			if (xval == null || yvals == null || yvals.Count == 0)
			{
				//No data to save
				MessageBox.Show("No data to export. Render the chart first.", "SQL Chart", MessageBoxButtons.OK, MessageBoxIcon.Warning);
				return;
			}

			//Build HTML document
			string html = @"<?xml version=""1.0"" encoding=""utf-8""?>
<html>
<head>
  <title>SQL Chart</title>
  <style type=""text/css"">
  html, body
  {
    font-family: Verdana, Tahoma, Arial;
  }
  
  pre
  {
    background-color: #F9F9F9;
    border: 1px dashed #777777;
    margin: 10px 0;
    padding: 15px;
  }
  
  th, td
  {
    text-align: right;
    padding-right: 10px;
  }
  
  th.first, td.first
  {
    text-align: left;
  }
  </style>
</head>
<body>
  <h1>SQL Chart Report</h1>
  <p><strong>Created:</strong> [CREATED]</p>
  <p><pre>[QUERY]</pre></p>
  <p><img src=""[IMAGE]"" alt=""chart"" /></p>
  <p><table>
[TH]
[TD]
  </table></p>
</body>
</html>
";

			var th = new StringBuilder();
			foreach (var header in headers)
			{
				string className = (th.Length == 0 ? " class=\"first\"" : "");
				th.Append("<th" + className + ">" + header + "</th>").AppendLine();
			}

			var builder = new StringBuilder();
			for (int i = 0; i < xval.Count; i++)
			{
				builder.AppendLine("<tr>");
				builder.AppendFormat("<td class=\"first\">{0}</td>", xval[i]).AppendLine();
				foreach (var yval in yvals)
				{
					builder.AppendFormat("<td>{0}</td>", yval[i]).AppendLine();
				}
				builder.AppendLine("</tr>");
			}

			//Replace constants
			html = html.Replace("[CREATED]", DateTime.Now.ToString());
			html = html.Replace("[QUERY]", this.Query);
			html = html.Replace("[TH]", "<tr>" + th.ToString() + "</tr>");
			html = html.Replace("[TD]", builder.ToString());

			//Save to file
			var dialog = new SaveFileDialog();
			dialog.Filter = "Report (*.html)|*.html";
			if (dialog.ShowDialog() == DialogResult.OK)
			{
				//Save image
				string imagePath = Path.ChangeExtension(dialog.FileName, "png");
				html = html.Replace("[IMAGE]", Path.GetFileName(imagePath)); //Relative path
				SaveImage(imagePath);

				//Save HTML
				File.WriteAllText(dialog.FileName, html);
			}
		}
		#endregion

		#region Protected methods
		protected void SaveImage(string path)
		{
			//Clear background
			chart.AntiAliasing = AntiAliasingStyles.All;
			chart.BackColor = Color.White;
			chart.BackSecondaryColor = Color.White;
			chart.BackGradientStyle = GradientStyle.None;

			//Save chart image to disk
			chart.SaveImage(path, ChartImageFormat.Png);

			//Restore background
			chart.BackColor = Color.WhiteSmoke;
			chart.BackSecondaryColor = Color.White; //Gainsboro, AliceBlue
			chart.BackGradientStyle = GradientStyle.TopBottom;
		}
		#endregion

		#region Event handlers
		private void popup_Opening(object sender, CancelEventArgs e)
		{
			itemChartType.DropDownItems.Clear();

			//Load all possible chart types to test
			//var names = Enum.GetNames(typeof(SeriesChartType));

			//Load only selected (most useful)
			string[] names = new string[] { "Point", "Line", "Spline", "Bar", "Column", "Area", "Pie", "Radar", "Pyramid" };
			foreach (string name in names)
			{
				var item = new ToolStripMenuItem(name);
				item.Tag = name;
				item.CheckOnClick = true;
				item.Checked = (name == this.ChartType.ToString());
				item.Click += new EventHandler(itemChartType_Click);
				itemChartType.DropDownItems.Add(item);
			}
		}

		private void itemChartType_Click(object sender, EventArgs e)
		{
			try
			{
				var item = sender as ToolStripMenuItem;
				var type = (SeriesChartType)Enum.Parse(typeof(SeriesChartType), item.Tag as string);
				this.ChartType = type;
				this.Generate(); //Refresh chart
			}
			catch
			{
			}
		}

		private void itemRefresh_Click(object sender, EventArgs e)
		{
			TryGenerate();
		}

		private void itemImage_Click(object sender, EventArgs e)
		{
			SaveImageDialog();
		}

		private void itemCSV_Click(object sender, EventArgs e)
		{
			SaveDataDialog();
		}

		private void itemHTML_Click(object sender, EventArgs e)
		{
			SaveReportDialog();
		}
		#endregion
	}
}
